package xyz.ag.DAO.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import xyz.ag.Beans.Book;
import xyz.ag.Beans.User;
import xyz.ag.Beans.Shoppingcart;
import xyz.ag.DAO.CartAndOrderDAOInterface;
import xyz.ag.utils.c3p0utils;

public class CartAndOrderDAOInterfaceImpl implements CartAndOrderDAOInterface {


	/**
	 * 添加购物车的页面方法 用来向购物车表中添加数据
	 * 
	 * 返回一个整形;来表示自己的修改的数字
	 */
	@Override
	public Integer addtocar(int bookid, Integer userid) {
		int number = CartAndOrderDAOInterfaceImpl.inthecar(bookid, userid);
		Date date = new Date();
		if(number==0){//判断购物车是否已经被添加{
			c3p0utils.executupdate("insert into shoppingcart(USERID,BOOKID,number,adddate) VALUES(?,?,1,?)", bookid,userid,date);
		}else{//添加了就加一个数
			c3p0utils.executupdate("update shoppingcart set number = ?,adddate=? where userid =? and bookid = ?",number++,date,bookid,userid );
		}
			
		return null;
	}

	/**
	 * 查询一个记录是否存在
	 * 当用户id和图书id都 重复的时候 返回之间记录的购物车的数据值
	 * 当不存在的时候 返回0
	 * @param userid 用户id 
	 * @param BookID 图书id
	 * @return
	 */
	private  static Integer inthecar(Integer userid,Integer BookID){
		Connection conn = c3p0utils.GetConnection();
		try {
			PreparedStatement ps= conn.prepareStatement("select userID,number FROM shoppingcart where BOOKID= ?");
			ps.setInt(1, BookID);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				if(rs.getString("userid")!=null) return rs.getInt("number");
			}
			c3p0utils.closeconn(rs, ps, conn);
		} catch (SQLException e) {
			System.err.println("用户登录数据库查询异常");
			e.printStackTrace();
		}
		return 0;
	}


	/**
	 * 传入用户id 用来对单个用户的购物车进行查询和操作
	 * 查询结果进行统计 查看共有多少条目存在
	 * 返回一个以String记录的integer值
	 */
	@Override
	public String getshpooingcarListNmuber(Integer userID) {
		Connection conn = c3p0utils.GetConnection();
		try {
			PreparedStatement ps= conn.prepareStatement("SELECT count(BookID) number  FROM  shoppingcart WHERE UserID = ?");
			ps.setInt(1, userID);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				if(rs.getInt("number")!=0) 
					return rs.getString("number");
			}
			c3p0utils.closeconn(rs, ps, conn);
		} catch (SQLException e) {
			System.err.println("用户登录数据库查询异常");
			e.printStackTrace();
		}
		return null;
	}

	
	/**
	 * 获得用户购物车的方法 封装一个ArrayList用来返回
	 * 对json支持的ajax函数 用户在加载完成之后立刻重载表格 加载数据
	 * @throws SQLException //Service处理sql异常
	 */
	@Override
	public ArrayList<Shoppingcart> getShoppingcarLimitpageList(Integer page, Integer number, Integer userID) throws SQLException {
		Connection conn = c3p0utils.GetConnection();
		
		ArrayList<Shoppingcart> alshopcar = new ArrayList<Shoppingcart>();
		
		PreparedStatement ps = conn.prepareStatement("SELECT id, UserID,BookID,number,adddate  FROM  shoppingcart  LIMIT ?,?");
		ps.setInt(1, page);
		ps.setInt(2,number);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			alshopcar.add(
			new Shoppingcart(rs.getString("number"),rs.getString("adddate"),
					rs.getString("BOOKID"),rs.getString("UserID"),rs.getString("id"))//封装起步
					);
		}
		c3p0utils.closeconn(rs, ps, conn);
		return alshopcar;
	}

	/**
	 * ajax操作的支持方法
	 * 在购物车页面的操作按钮中的添加按钮的支持方法
	 * 用来增加购物车商品的数量
	 */
	@Override
	public Integer shoppingcarknumberentryadd(Integer entryID) {
		Integer number = getshoppingcarknumberentryByID(entryID);
		Date date = new Date(); 
		c3p0utils.executupdate("update shoppingcart set number = ?,adddate = ? where id = ?", number+1,date, entryID);
		return number++;
	}
	
	/**
	 * ajax操作的支持方法
	 * 在购物车页面的操作按钮中的添加按钮的支持方法
	 * 用来减少购物车商品的数量
	 * 
	 * 如果商品数为0 则不可以在减少
	 * 返回一个旧值与新值的差值
	 */
	@Override
	public Integer shoppingcarknumberentrysub(Integer entryID) {
		Integer number = getshoppingcarknumberentryByID(entryID);
		Date date = new Date(); 
		c3p0utils.executupdate("update shoppingcart set number = ?,adddate=? where id = ?", number-1,date, entryID);
		return getshoppingcarknumberentryByID(entryID)-number;
	}
	
	/**
	 * 内部数据聚合操作方法 
	 * 用来根据ID查询出来订单的数量
	 * @param entryID
	 * @return
	 */
	public static Integer getshoppingcarknumberentryByID(Integer entryID){
		Connection conn = c3p0utils.GetConnection();
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("SELECT number  FROM  shoppingcart WHERE ID = ?");
		ps.setInt(1, entryID);
		ResultSet rs = ps.executeQuery();
			while(rs.next()){
				if(rs.getInt("number")!=0) 
					return rs.getInt("number");
				}
			c3p0utils.closeconn(rs, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
		
	}

	@Override
	public Integer shoppingcarknumberentryrem(Integer entryID) {
		return c3p0utils.executupdate("delete from shoppingcart where id = ?", entryID);
	
	}
}








